-- ============================================
-- 1. 自动更新时间戳函数
-- 功能：所有表更新时刷新 updated_at
-- ============================================
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- users 表时间戳触发器
DROP TRIGGER IF EXISTS trg_users_updated_at ON users;
CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- points_rewards 表时间戳触发器
DROP TRIGGER IF EXISTS trg_points_rewards_updated_at ON points_rewards;
CREATE TRIGGER trg_points_rewards_updated_at
BEFORE UPDATE ON points_rewards
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- rewards 表时间戳触发器
DROP TRIGGER IF EXISTS trg_rewards_updated_at ON rewards;
CREATE TRIGGER trg_rewards_updated_at
BEFORE UPDATE ON rewards
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- sessions 表时间戳触发器
DROP TRIGGER IF EXISTS trg_sessions_updated_at ON sessions;
CREATE TRIGGER trg_sessions_updated_at
BEFORE UPDATE ON sessions
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS points_rewards_after_update_trigger ON points_rewards;
DROP FUNCTION IF EXISTS points_rewards_after_update();

-- ============================================
-- 存储过程：兑换奖励
-- ============================================
CREATE OR REPLACE PROCEDURE redeem_reward(
    p_userid BIGINT,
    p_reward_id BIGINT,
    p_quantity INT DEFAULT 1
)
LANGUAGE plpgsql
AS $$
DECLARE
    reward_points INT;      -- 单个奖品所需积分
    current_points INT;     -- 用户当前积分
    current_stock INT;      -- 奖品当前库存
    reward_name TEXT;       -- 奖品名称
BEGIN

    SELECT points_required, stock, name
    INTO reward_points, current_stock, reward_name
    FROM rewards
    WHERE reward_id = p_reward_id;

    IF reward_points IS NULL THEN
        RAISE EXCEPTION '兑换的奖品不存在（reward_id=%）', p_reward_id;
    END IF;

    IF p_quantity < 1 THEN
        RAISE EXCEPTION '兑换数量必须大于0';
    END IF;

    SELECT points INTO current_points
    FROM points_rewards
    WHERE userid = p_userid;

    IF current_points IS NULL THEN
        RAISE EXCEPTION '用户积分记录不存在（userid=%）', p_userid;
    END IF;

    IF current_points < reward_points * p_quantity THEN
        RAISE EXCEPTION '用户积分不足（当前%, 需%）', current_points, reward_points * p_quantity;
    END IF;

    IF current_stock < p_quantity THEN
        RAISE EXCEPTION '奖品库存不足（当前%, 需%）', current_stock, p_quantity;
    END IF;

    UPDATE points_rewards
    SET points = points - reward_points * p_quantity
    WHERE userid = p_userid;

    UPDATE rewards
    SET stock = stock - p_quantity
    WHERE reward_id = p_reward_id;

    INSERT INTO point_records(
        userid, points_change, source_type, related_id, quantity, description, created_at
    )
    VALUES (
        p_userid,
        - (reward_points * p_quantity),
        'REWARD_REDEMPTION',
        p_reward_id,
        p_quantity,
        format('兑换奖品：%s 数量：%s', reward_name, p_quantity),
        NOW()
    );
END;
$$;

-- ============================================
-- 触发器函数：新建session增加100积分
-- ============================================
CREATE OR REPLACE FUNCTION add_points_on_session_insert()
RETURNS TRIGGER AS $$
BEGIN
    -- 更新用户积分
    INSERT INTO points_rewards(userid, points, updated_at)
    VALUES (NEW.userid, 100, NOW())
    ON CONFLICT (userid) DO UPDATE
        SET points = points + 100,
            updated_at = NOW();

    -- 插入积分记录
    INSERT INTO point_records(userid, points_change, source_type, related_id, description, created_at)
    VALUES (NEW.userid, 100, 'SESSION_CREATED', NEW.sessionid, '创建新会话奖励积分', NOW());

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 触发器：插入session时触发
CREATE TRIGGER trigger_add_points_on_session_insert
AFTER INSERT ON sessions
FOR EACH ROW
EXECUTE FUNCTION add_points_on_session_insert();

-- ============================================
-- 触发器函数：新建session增加100积分
-- ============================================
CREATE OR REPLACE FUNCTION add_points_on_session_insert()
RETURNS TRIGGER AS $$
BEGIN
    -- 更新用户积分，避免字段歧义
    INSERT INTO points_rewards(userid, points, updated_at)
    VALUES (NEW.userid, 100, NOW())
    ON CONFLICT (userid) DO UPDATE
        SET points = points_rewards.points + EXCLUDED.points,
            updated_at = NOW();

    -- 插入积分记录
    INSERT INTO point_records(userid, points_change, source_type, related_id, description, created_at)
    VALUES (NEW.userid, 100, 'SESSION_CREATED', NEW.sessionid, '创建新会话奖励积分', NOW());

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 触发器：插入session时触发
DROP TRIGGER IF EXISTS trigger_add_points_on_session_insert ON sessions;
CREATE TRIGGER trigger_add_points_on_session_insert
AFTER INSERT ON sessions
FOR EACH ROW
EXECUTE FUNCTION add_points_on_session_insert();


-- ============================================
-- 触发器函数：session更新feedback或rating增加200积分
-- ============================================
CREATE OR REPLACE FUNCTION add_points_on_session_feedback()
RETURNS TRIGGER AS $$
BEGIN
    -- 判断 feedback 或 rating 是否由 NULL 变为非 NULL
    IF (OLD.feedback IS NULL AND NEW.feedback IS NOT NULL) OR
       (OLD.rating IS NULL AND NEW.rating IS NOT NULL) THEN

        -- 更新用户积分
        INSERT INTO points_rewards(userid, points, updated_at)
        VALUES (NEW.userid, 200, NOW())
        ON CONFLICT (userid) DO UPDATE
            SET points = points_rewards.points + EXCLUDED.points,
                updated_at = NOW();

        -- 插入积分记录
        INSERT INTO point_records(userid, points_change, source_type, related_id, description, created_at)
        VALUES (NEW.userid, 200, 'SESSION_FEEDBACK', NEW.sessionid, '提交会话反馈奖励积分', NOW());
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 触发器：更新session时触发
DROP TRIGGER IF EXISTS trigger_add_points_on_session_feedback ON sessions;
CREATE TRIGGER trigger_add_points_on_session_feedback
AFTER UPDATE ON sessions
FOR EACH ROW
EXECUTE FUNCTION add_points_on_session_feedback();



-- 触发器：用量和预算统计
CREATE OR REPLACE FUNCTION trg_sessions_stats()
RETURNS TRIGGER AS $$
DECLARE
    budget_start_idx INTEGER;
    budget_end_idx INTEGER;
    budget_json_str TEXT;
    budget_json JSONB;
    budget_range NUMERIC[];
    min_budget NUMERIC;
    max_budget NUMERIC;
    w NUMERIC;
    month_str CHAR(7);
BEGIN
    -- 月份统计
    month_str := to_char(NEW.created_at, 'YYYY-MM');
    INSERT INTO use_count(month, total_count)
    VALUES (month_str, 1)
    ON CONFLICT (month)
    DO UPDATE SET total_count = use_count.total_count + 1;

    -- 检查prompt中是否包含budget字段
    IF NEW.prompt IS NULL OR position('"budget":' in NEW.prompt) = 0 THEN
        RETURN NEW;  -- 没有budget字段，跳过预算统计
    END IF;

    -- 提取budget JSON部分
    budget_start_idx := position('"budget":' in NEW.prompt);
    budget_end_idx := position(']' in substring(NEW.prompt from budget_start_idx));
    
    IF budget_end_idx = 0 THEN
        RETURN NEW;  -- 没有完整的budget数组，跳过
    END IF;

    -- 提取budget JSON字符串
    budget_json_str := substring(NEW.prompt from budget_start_idx for budget_end_idx);
    budget_json_str := regexp_replace(budget_json_str, '"budget":\s*', '');
    
    -- 尝试解析为JSON
    BEGIN
        budget_json := budget_json_str::jsonb;
    EXCEPTION WHEN others THEN
        RETURN NEW;  -- 非合法JSON，跳过预算统计
    END;

    -- 检查并提取预算范围
    IF jsonb_typeof(budget_json) = 'array' 
       AND jsonb_array_length(budget_json) = 2 THEN
        budget_range := ARRAY(
            SELECT jsonb_array_elements_text(budget_json)::NUMERIC
        );
        min_budget := budget_range[1];
        max_budget := budget_range[2];
    ELSE
        RETURN NEW;  -- 不是有效的预算数组，跳过
    END IF;

    -- 按区间累积预算权重
    IF max_budget <= 10 THEN
        w := 1;
        INSERT INTO budget_count(budget, total_count)
        VALUES ('10-', w)
        ON CONFLICT (budget) DO UPDATE SET total_count = budget_count.total_count + w;
    END IF;

    IF max_budget > 10 THEN
        w := GREATEST(LEAST(max_budget,20)-GREATEST(min_budget,10),0)/NULLIF(max_budget-min_budget,0);
        IF w>0 THEN
            INSERT INTO budget_count(budget, total_count)
            VALUES ('10-20', w)
            ON CONFLICT (budget) DO UPDATE SET total_count = budget_count.total_count + w;
        END IF;
    END IF;

    IF max_budget > 20 THEN
        w := GREATEST(LEAST(max_budget,30)-GREATEST(min_budget,20),0)/NULLIF(max_budget-min_budget,0);
        IF w>0 THEN
            INSERT INTO budget_count(budget, total_count)
            VALUES ('20-30', w)
            ON CONFLICT (budget) DO UPDATE SET total_count = budget_count.total_count + w;
        END IF;
    END IF;

    IF max_budget > 30 THEN
        w := GREATEST(LEAST(max_budget,40)-GREATEST(min_budget,30),0)/NULLIF(max_budget-min_budget,0);
        IF w>0 THEN
            INSERT INTO budget_count(budget, total_count)
            VALUES ('30-40', w)
            ON CONFLICT (budget) DO UPDATE SET total_count = budget_count.total_count + w;
        END IF;
    END IF;

    IF max_budget > 40 THEN
        w := GREATEST(LEAST(max_budget,50)-GREATEST(min_budget,40),0)/NULLIF(max_budget-min_budget,0);
        IF w>0 THEN
            INSERT INTO budget_count(budget, total_count)
            VALUES ('40-50', w)
            ON CONFLICT (budget) DO UPDATE SET total_count = budget_count.total_count + w;
        END IF;
    END IF;

    IF max_budget > 50 THEN
        w := (max_budget - GREATEST(min_budget,50))/NULLIF(max_budget-min_budget,0);
        IF w>0 THEN
            INSERT INTO budget_count(budget, total_count)
            VALUES ('50+', w)
            ON CONFLICT (budget) DO UPDATE SET total_count = budget_count.total_count + w;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 删除原有触发器（如果存在）
DROP TRIGGER IF EXISTS trg_sessions_stats_insert ON sessions;

-- 创建触发器
CREATE TRIGGER trg_sessions_stats_insert
AFTER INSERT ON sessions
FOR EACH ROW
EXECUTE FUNCTION trg_sessions_stats();